VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "clsReports"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = True
Option Explicit

Public Function NewUsers(ByVal sBusUnit As String, ByVal dtDateFrom As String, ByVal dtDateTo As String) As recordSet

    Dim cnConnection As ADODB.Connection
    Dim rsRecordSet As ADODB.recordSet
    
    Dim cmd As ADODB.Command
    
    Dim dtNewDateFrom As Date
    Dim dtNewDateTo As Date
    
    dtNewDateFrom = DateSerial(Left$(dtDateFrom, 4), Mid$(dtDateFrom, 5, 2), Right$(dtDateFrom, 2))
    dtNewDateTo = DateSerial(Left$(dtDateTo, 4), Mid$(dtDateTo, 5, 2), Right$(dtDateTo, 2))
    
    
    Set cnConnection = New ADODB.Connection
    
    With cnConnection
        .CursorLocation = adUseClient
        .IsolationLevel = adXactBrowse
        .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
        .Open
    End With
    
    Set rsRecordSet = New ADODB.recordSet
        rsRecordSet.CursorLocation = adUseClient
        rsRecordSet.CursorType = adOpenStatic
        
    Set cmd = New ADODB.Command
    cmd.CommandText = "dir_reports_new_users"
    cmd.CommandType = adCmdStoredProc
    cmd.ActiveConnection = cnConnection
        
        cmd.Parameters.Append cmd.CreateParameter("BusUnit", adVarChar, adParamInput, 6, sBusUnit)
        cmd.Parameters.Append cmd.CreateParameter("FromMonth", adDBTimeStamp, adParamInput, , dtNewDateFrom)
        cmd.Parameters.Append cmd.CreateParameter("ToMonth", adDBTimeStamp, adParamInput, , dtNewDateTo)
        
    Set rsRecordSet = cmd.Execute
    
    Set rsRecordSet.ActiveConnection = Nothing
    
    Set NewUsers = rsRecordSet
    
    
    cnConnection.Close
    Set cnConnection = Nothing
    

End Function

Public Function Requests(ByVal sBusUnit As String, ByVal dtDateFrom As String, ByVal dtDateTo As String) As recordSet

    Dim cnConnection As ADODB.Connection
    Dim rsRecordSet As ADODB.recordSet
    
    
    Dim cmd As ADODB.Command
    
    Dim dtNewDateFrom As Date
    Dim dtNewDateTo As Date
    
    dtNewDateFrom = DateSerial(Left$(dtDateFrom, 4), Mid$(dtDateFrom, 5, 2), Right$(dtDateFrom, 2))
    dtNewDateTo = DateSerial(Left$(dtDateTo, 4), Mid$(dtDateTo, 5, 2), Right$(dtDateTo, 2))
    
    
    Set cnConnection = New ADODB.Connection
    
    With cnConnection
        .CursorLocation = adUseClient
        .IsolationLevel = adXactBrowse
        .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
        .Open
    End With
    
    Set rsRecordSet = New ADODB.recordSet
        rsRecordSet.CursorLocation = adUseClient
        rsRecordSet.CursorType = adOpenStatic
        
    Set cmd = New ADODB.Command
    cmd.CommandText = "dir_reports_stats"
    cmd.CommandType = adCmdStoredProc
    cmd.ActiveConnection = cnConnection
        
        cmd.Parameters.Append cmd.CreateParameter("BusUnit", adVarChar, adParamInput, 6, sBusUnit)
        cmd.Parameters.Append cmd.CreateParameter("FromMonth", adDBTimeStamp, adParamInput, , dtNewDateFrom)
        cmd.Parameters.Append cmd.CreateParameter("ToMonth", adDBTimeStamp, adParamInput, , dtNewDateTo)
        
    Set rsRecordSet = cmd.Execute
    
    Set rsRecordSet.ActiveConnection = Nothing
    
    Set Requests = rsRecordSet
    
    
    cnConnection.Close
    Set cnConnection = Nothing
    
    
End Function

Public Function GetList() As recordSet

    Dim cnConnection As ADODB.Connection
    Dim rsRecordSet As ADODB.recordSet
    
    
    Dim cmd As ADODB.Command
    
    
    Set cnConnection = New ADODB.Connection
    
    With cnConnection
        .CursorLocation = adUseClient
        .IsolationLevel = adXactBrowse
        .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
        .Open
    End With
    
    Set rsRecordSet = New ADODB.recordSet
        rsRecordSet.CursorLocation = adUseClient
        rsRecordSet.CursorType = adOpenStatic
        
    Set cmd = New ADODB.Command
        cmd.CommandText = "dir_reports_getlist"
        cmd.CommandType = adCmdStoredProc
        cmd.ActiveConnection = cnConnection
        
    Set rsRecordSet = cmd.Execute
    Set rsRecordSet.ActiveConnection = Nothing
    Set GetList = rsRecordSet
    
    cnConnection.Close
    Set cnConnection = Nothing
End Function

Public Function GetFields(ByVal nReportID As String) As recordSet

    Dim cnConnection As ADODB.Connection
    Dim rsRecordSet As ADODB.recordSet
    
    
    Dim cmd As ADODB.Command
    
    
    Set cnConnection = New ADODB.Connection
    
    With cnConnection
        .CursorLocation = adUseClient
        .IsolationLevel = adXactBrowse
        .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
        .Open
    End With
    
    Set rsRecordSet = New ADODB.recordSet
        rsRecordSet.CursorLocation = adUseClient
        rsRecordSet.CursorType = adOpenStatic
        
    Set cmd = New ADODB.Command
        cmd.CommandText = "dir_reports_getfields"
        cmd.CommandType = adCmdStoredProc
        cmd.ActiveConnection = cnConnection
        
    cmd.Parameters.Append cmd.CreateParameter("report_id", adDouble, adParamInput, 10, nReportID)
        
    Set rsRecordSet = cmd.Execute
    Set rsRecordSet.ActiveConnection = Nothing
    Set GetFields = rsRecordSet
    
    cnConnection.Close
    Set cnConnection = Nothing
End Function

Public Function Generate(ByVal sProcedureName As String, _
                         ByVal sFieldNames As String, _
                         ByVal sFieldTypes As String, _
                         ByVal sFieldValues As String) As recordSet

On Error GoTo PROC_ERROR

    Dim cnConnection As ADODB.Connection
    Dim rsRecordSet As ADODB.recordSet
    
    
    Dim cmd As ADODB.Command
    
    Dim arFieldNames As Variant
    Dim arFieldTypes As Variant
    Dim arFieldValues As Variant
                         
    Dim dtDateValue As Date
    Dim sStringValue As String
    Dim nNumberValue As Long
    
    Dim iParameters As Long
    
    
    Set cnConnection = New ADODB.Connection
    Set rsRecordSet = New ADODB.recordSet
    Set cmd = New ADODB.Command
    
    With cnConnection
        .CursorLocation = adUseClient
        .IsolationLevel = adXactBrowse
        .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
        .Open
    End With
    
    arFieldNames = Split(sFieldNames, "|")
    arFieldTypes = Split(UCase(sFieldTypes), "|")
    arFieldValues = Split(sFieldValues, "|")
    
    If UBound(arFieldNames) <> UBound(arFieldTypes) Or UBound(arFieldNames) <> UBound(arFieldValues) Then
        Err.Raise 0, "KeyTax.clsReports.Generate", "Number of names, types and values does not match"
    End If
    
    'Set up the stored procedure with the values that were passed in
    cmd.CommandText = sProcedureName
    cmd.CommandType = adCmdStoredProc
    cmd.ActiveConnection = cnConnection
    
    'Set up the parameters for the stored procedure
    For iParameters = 0 To UBound(arFieldNames)
        Select Case arFieldTypes(iParameters)
            Case "NUMBER"
                nNumberValue = CLng(arFieldValues(iParameters))
                cmd.Parameters.Append cmd.CreateParameter(arFieldNames(iParameters), adNumeric, adParamInput, , nNumberValue)
            Case "STRING"
                sStringValue = CStr(arFieldValues(iParameters))
                cmd.Parameters.Append cmd.CreateParameter(arFieldNames(iParameters), adVarChar, adParamInput, 255, sStringValue)
            Case "DATE"
                dtDateValue = DateSerial(Left$(arFieldValues(iParameters), 4), Mid$(arFieldValues(iParameters), 5, 2), Right$(arFieldValues(iParameters), 2))
                cmd.Parameters.Append cmd.CreateParameter(arFieldNames(iParameters), adDBTimeStamp, adParamInput, , dtDateValue)
            Case Else
                Err.Raise 0, "KeyTax.clsReports.Generate", arFieldTypes(iParameters) & " data type is not supported"
        End Select
    Next
        
    'Execute the proc and return the results
    Set rsRecordSet = cmd.Execute
    Set rsRecordSet.ActiveConnection = Nothing
    Set Generate = rsRecordSet
    
    
PROC_EXIT:
    cnConnection.Close
    
    Set cnConnection = Nothing
    Set cmd = Nothing
    Set rsRecordSet = Nothing
    
    Exit Function
    
PROC_ERROR:
    Err.Raise Err.Number, "KeyTax.clsReports.Generate", Err.Description
    GoTo PROC_EXIT
    
End Function
Public Function finalisedCancelledReqReport(ByVal sBusUnit As String, ByVal dtDateFrom As String, ByVal dtDateTo As String) As recordSet

    Dim cnConnection As ADODB.Connection
    Dim rsRecordSet As ADODB.recordSet
    
    Dim cmd As ADODB.Command
    
    Dim dtNewDateFrom As Date
    Dim dtNewDateTo As Date
    
    dtNewDateFrom = DateSerial(Left$(dtDateFrom, 4), Mid$(dtDateFrom, 5, 2), Right$(dtDateFrom, 2))
    dtNewDateTo = DateSerial(Left$(dtDateTo, 4), Mid$(dtDateTo, 5, 2), Right$(dtDateTo, 2))
    
    
    Set cnConnection = New ADODB.Connection
    
    With cnConnection
        .CursorLocation = adUseClient
        .IsolationLevel = adXactBrowse
        .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
        .Open
    End With
    
    Set rsRecordSet = New ADODB.recordSet
        rsRecordSet.CursorLocation = adUseClient
        rsRecordSet.CursorType = adOpenStatic
        
    Set cmd = New ADODB.Command
    cmd.CommandText = "dir_sl_fin_can_report"
    cmd.CommandType = adCmdStoredProc
    cmd.ActiveConnection = cnConnection
        
        cmd.Parameters.Append cmd.CreateParameter("BusUnit", adVarChar, adParamInput, 6, sBusUnit)
        cmd.Parameters.Append cmd.CreateParameter("FromMonth", adDBTimeStamp, adParamInput, , dtNewDateFrom)
        cmd.Parameters.Append cmd.CreateParameter("ToMonth", adDBTimeStamp, adParamInput, , dtNewDateTo)
        
    Set rsRecordSet = cmd.Execute
    
    Set rsRecordSet.ActiveConnection = Nothing
    
    Set finalisedCancelledReqReport = rsRecordSet
    
    
    cnConnection.Close
    Set cnConnection = Nothing
    

End Function
Public Function DeclinedRequestsReport(ByVal sBusUnit As String, ByVal dtDateFrom As String, ByVal dtDateTo As String) As recordSet

    Dim cnConnection As ADODB.Connection
    Dim rsRecordSet As ADODB.recordSet
    
    Dim cmd As ADODB.Command
    
    Dim dtNewDateFrom As Date
    Dim dtNewDateTo As Date
    
    dtNewDateFrom = DateSerial(Left$(dtDateFrom, 4), Mid$(dtDateFrom, 5, 2), Right$(dtDateFrom, 2))
    dtNewDateTo = DateSerial(Left$(dtDateTo, 4), Mid$(dtDateTo, 5, 2), Right$(dtDateTo, 2))
    
    
    Set cnConnection = New ADODB.Connection
    
    With cnConnection
        .CursorLocation = adUseClient
        .IsolationLevel = adXactBrowse
        .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
        .Open
    End With
    
    Set rsRecordSet = New ADODB.recordSet
        rsRecordSet.CursorLocation = adUseClient
        rsRecordSet.CursorType = adOpenStatic
        
    Set cmd = New ADODB.Command
    cmd.CommandText = "dir_sl_declined_report"
    cmd.CommandType = adCmdStoredProc
    cmd.ActiveConnection = cnConnection
        
        cmd.Parameters.Append cmd.CreateParameter("BusUnit", adVarChar, adParamInput, 6, sBusUnit)
        cmd.Parameters.Append cmd.CreateParameter("FromMonth", adDBTimeStamp, adParamInput, , dtNewDateFrom)
        cmd.Parameters.Append cmd.CreateParameter("ToMonth", adDBTimeStamp, adParamInput, , dtNewDateTo)
        
    Set rsRecordSet = cmd.Execute
    
    Set rsRecordSet.ActiveConnection = Nothing
    
    Set DeclinedRequestsReport = rsRecordSet
    
    
    cnConnection.Close
    Set cnConnection = Nothing
    

End Function

